CREATE VIEW vDonorRelationship AS
    SELECT Relationship.ID                                  ID,  
           Relationship.TARGET_ID                         TID,
           Relationship.RELATION_TYPE                 RTYPE,
           Relationship.TARGET_RELATION_TYPE  TRTYPE,
           Name_1.FULL_NAME                               NAME,
           Relationship.TARGET_NAME                   TNAME,
           Relationship.TITLE                                  TITLE,
           Relationship.EFFECTIVE_DATE               EFFDATE,
           Relationship.THRU_DATE                        THRUDATE,
           Relationship_Types.DESCRIPTION         RTDESC,
           Name_1.COMPANY                                 COMPANY,
           Name_1.COMPANY_RECORD                 COREC
    FROM Relationship INNER JOIN Name  ON
                             Relationship.ID = Name.ID
              LEFT OUTER JOIN Name Name_1 ON   
                             Relationship.TARGET_ID = Name_1.ID
              INNER JOIN  Relationship_Types ON
                             Relationship.RELATION_TYPE = Relationship_Types.RELATION_TYPE
  UNION
    SELECT Relationship.TARGET_ID                   ID,
           Relationship.ID                                        TID,    
           Relationship.TARGET_RELATION_TYPE  RTYPE,
           Relationship.RELATION_TYPE                 TRTYPE,
           Name_1.FULL_NAME                               NAME,
           Relationship.TARGET_NAME                   TNAME,
           Relationship.TITLE                                   TITLE,
           Relationship.EFFECTIVE_DATE               EFFDATE,
           Relationship.THRU_DATE                        THRUDATE,
           Relationship_Types.DESCRIPTION         RTDESC,    
           Name_1.COMPANY                                 COMPANY,
           Name_1.COMPANY_RECORD                 COREC
    FROM Relationship INNER JOIN Name ON  
                  Relationship.TARGET_ID = Name.ID
         LEFT OUTER JOIN Name Name_1  ON  
                  Relationship.ID = Name_1.ID
         INNER JOIN  Relationship_Types ON
                  Relationship.TARGET_RELATION_TYPE = Relationship_Types.RELATION_TYPE

GO
GRANT REFERENCES ON  [dbo].[vDonorRelationship] TO [IMIS]
GRANT SELECT ON  [dbo].[vDonorRelationship] TO [IMIS]
GRANT INSERT ON  [dbo].[vDonorRelationship] TO [IMIS]
GRANT DELETE ON  [dbo].[vDonorRelationship] TO [IMIS]
GRANT UPDATE ON  [dbo].[vDonorRelationship] TO [IMIS]
GO